/**
	Author	    : Ravikanth
	Date	    : Sep 30th 2004
	Description : Returns the list of Vendors and their address Id's	
	Usage       : exec sp_get_Vendor_address_summary ''
			select * from Address where Address_Id = 746
*/
CREATE  proc sp_get_vendor_address_summary
(
@vendor_Name varchar(50)
)
as
begin

declare @vendor_address_details Table( Row_Id int IDENTITY, Row_Description char(30), vendor_Id int, Address_Id char(4), Number varchar(50), Name_or_Address1 varchar(50), Dex_Row_Id char(4) )

--Inserting Vendor Details
insert into @vendor_address_details( Row_Description, Vendor_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id ) select 'Vendor', Vendor.Vendor_Id, '', Vendor.V_Number, Vendor.V_Name, ISNULL( convert( char, CT_GP_Vendor_Mapping.GP_Vendor_Id ), '' ) from Vendor left join CT_GP_Vendor_Mapping on Vendor.Vendor_Id = CT_GP_Vendor_Mapping.CT_Vendor_Id where Vendor.V_Name like @Vendor_Name + '%' AND V_IS_DELETED = 'N' order by Vendor.Vendor_Id 

--Inserting Default Billing Address Details
insert into @vendor_address_details( Row_Description, Vendor_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id ) select 'Default Billing Address', Vendor.Vendor_Id, Vendor.Address_Id, '', Address.Address_1, ISNULL( convert( char, CT_GP_Vendor_Address_Mapping.GP_Address_Id ), '' ) from Vendor join Address on Vendor.Address_Id = Address.Address_Id left join CT_GP_Vendor_Address_Mapping on Vendor.Address_Id = CT_GP_Vendor_Address_Mapping.GP_Address_Id where Vendor.V_Name like @Vendor_Name + '%' AND V_IS_DELETED = 'N' 

--Inserting Default Shipping Address Details
insert into @vendor_address_details( Row_Description, Vendor_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id ) select  DISTINCT 'Default Shipping Address', Vendor.Vendor_Id, LOCATION.Address_Id, '', Address.Address_1, ISNULL( convert( char, CT_GP_Vendor_Address_Mapping.GP_ADDRESS_Id ), '' ) from Vendor join Location on vendor.Vendor_Id = Location.Vendor_ID JOIN Address on LOCATION.Address_Id = Address.Address_Id left join CT_GP_Vendor_Address_Mapping on Location.Address_Id = CT_GP_Vendor_Address_Mapping.CT_Address_Id where Vendor.V_Name like @Vendor_Name + '%' AND V_IS_DELETED = 'N' 


--Displaying the details
select Row_Description, Vendor_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id from @vendor_address_details order by Vendor_Id, Row_Id

end --End of Proc